package com.wyd.utils;


import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;

/**
 * @author 文永达
 * @see  //数据库工具类
 * @time 2021-2-3 上午11:21:31
 * @version 1.0
 */
public class DBUtil {
    private static String driver = "com.mysql.cj.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/jiaowuglxt?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
    private static String user = "root";
    private static String password = "Wyd210213";

    private static Connection conn = null;
    private static Statement stmt = null;
    private static PreparedStatement pstmt = null;
    private static ResultSet rs = null;

    /**
     * @description: <br>
     * 〈数据库准备连接〉
     * @Param: []
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/2/7 21:36
     */
    static {
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url,user,password);
        } catch (ClassNotFoundException e){
            e.printStackTrace();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

    /**
     * @description: <br>
     * 〈查询未预处理〉
     * @Param: [sql]
     * @Return: java.sql.ResultSet
     * @Author: 文永达
     * @Date: 2021/2/7 21:36
     */
    public ResultSet querySQL(String sql){
        try {
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(sql);
        } catch (SQLException e){
            e.printStackTrace();
        }
        return rs;
    }

    /**
     * @description: <br>
     * 〈查询预处理〉
     * @Param: [sql]
     * @Return: java.sql.ResultSet
     * @Author: 文永达
     * @Date: 2021/2/7 21:36
     */
    public ResultSet querySQLPs(String sql){
        try {
            pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
            rs = pstmt.executeQuery();
        } catch (SQLException e){
            e.printStackTrace();
        }
        return rs;
    }


    /**
     * @description: <br>
     * 〈更新未预处理〉
     * @Param: [sql]
     * @Return: int
     * @Author: 文永达
     * @Date: 2021/2/7 21:36
     */
    public int updateSQL(String sql){
        int i = 0;
        try {
            stmt = conn.createStatement();
            i = stmt.executeUpdate(sql);
        } catch (SQLException e){
            e.printStackTrace();
        }
        return i;
    }

    /**
     * @description: <br>
     * 〈更新预处理〉
     * @Param: [sql]
     * @Return: int
     * @Author: 文永达
     * @Date: 2021/2/7 21:37
     */
    public int updateSQLPs(String sql){
        int i = 0;
        try {
            pstmt = conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
        } catch (SQLException e){
            e.printStackTrace();
        }
        return i;
    }

    /**
     * @description: <br>
     * 〈查询数据库结果集放到内存中，得到缓存结果〉
     * @Param: [sql]
     * @Return: java.util.ArrayList<java.util.HashMap<java.lang.String,java.lang.Object>>
     * @Author: 文永达
     * @Date: 2021/2/7 19:23
     */
    public ArrayList<HashMap<String,Object>> queryForList(String sql){
        ArrayList<HashMap<String,Object>> list = new ArrayList<HashMap<String, Object>>();
        try {
            pstmt = conn.prepareStatement(sql);
//            stmt = conn.createStatement();
            if (sql.startsWith("select")||sql.startsWith("SELECT")){
//                rs = stmt.executeQuery(sql);
                rs = pstmt.executeQuery();
                ResultSetMetaData rsmd = rs.getMetaData();
                while (rs.next()){
                    HashMap<String,Object> map = new HashMap<String, Object>();
                    for(int i = 1;i<= rsmd.getColumnCount();i++){
                        String columnName = rsmd.getColumnName(i);
                        Object columnValue = rs.getObject(columnName);
                        map.put(columnName,columnValue);
                    }
                    list.add(map);
                }
            } else {
                System.out.println("对不起本方法只支持select语句");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return list;
    }

    /**
     * @description: <br>
     * 〈〉
     * @Param: [sql]
     * @Return: java.util.ArrayList<java.util.HashMap<java.lang.String,?>>
     * @Author: 文永达
     * @Date: 2021/2/9 8:51
     */
    public ArrayList<HashMap<String,?>>queryForList1(String sql){
        ArrayList<HashMap<String,?>> list = new ArrayList<HashMap<String, ?>>();

        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()){
                HashMap<String,Object> map = new HashMap<String, Object>();
                for (int i = 1; i<=rsmd.getColumnCount();i++){
                    String columnName = rsmd.getColumnLabel(i);
                    if(rsmd.getColumnType(i) == Types.INTEGER){
                        int columnValue = rs.getInt(columnName);
                        map.put(columnName,columnValue);
                    }
                    if(rsmd.getColumnType(i) == Types.VARCHAR){
                        String columnValue = rs.getString(columnName);
                        map.put(columnName,columnValue);
                    }
                    if(rsmd.getColumnType(i) == Types.DOUBLE){
                        Double columnValue = rs.getDouble(columnName);
                        map.put(columnName,columnValue);
                    }
                    if(rsmd.getColumnType(i) == Types.CHAR){
                        String columnValue = rs.getString(columnName);
                        map.put(columnName,columnValue);
                    }
                }
                list.add(map);
                close();
            }
        } catch (SQLException e){
            e.printStackTrace();
        }
        return list;
    }
    /**
     * @description: <br>
     * 〈数据库关闭〉
     * @Param: []
     * @Return: void
     * @Author: 文永达
     * @Date: 2021/2/9 8:49
     */
    private void close() {
        try {
            if (null != rs)
                rs.close();
            if (null != stmt)
                stmt.close();
            if (null != conn)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
